﻿<?php
 session_start();
 
class DataBase {

	public $last_query;

	private $_connection;
	private $_magic_quotes_active;
	private $_real_escape_string_exists;

	function __construct() {
		$this->open_connection();
		$this->_magic_quotes_active = get_magic_quotes_gpc();
		$this->_real_escape_string_exists = function_exists( "mysql_real_escape_string" );
	}

	public function open_connection() {
		$this->_connection = mysql_connect(DB_HOST, DB_USER, DB_PASS);
		if(!$this->_connection)
			die('Database connection failed: ' . mysql_error());
		else {
			$db_select = mysql_select_db(DB_NAME, $this->_connection);
			if(!$db_select)
				die('Database selection failed: ' . mysql_error());
		}
	}

	public function close_connection() {
		if(isset($this->_connection)) {
			mysql_close($this->_connection);
			unset($this->_connection);
		}
	}

	public function query($sql) {
		$this->last_query = $sql;
		$result = mysql_query($sql, $this->_connection);
		$this->_confirm_query($result);
		return $result;
	}

	public function escape_value( $value ) {
		if( $this->_real_escape_string_exists ) { // PHP v4.3.0 or higher
			// undo any magic quote effects so mysql_real_escape_string can do the work
			if( $this->_magic_quotes_active ) { $value = stripslashes( $value ); }
			$value = mysql_real_escape_string( $value );
		} else { // before PHP v4.3.0
			// if magic quotes aren't already on then add slashes manually
			if( !$this->_magic_quotes_active ) { $value = addslashes( $value ); }
			// if magic quotes are active, then the slashes already exist
		}
		return $value;
	}
	
	public function insert_id() {
		return mysql_insert_id($this->_connection);
	}

	public function affected_rows() {
		return mysql_affected_rows($this->_connection);
	}

	private function _confirm_query($result) {
		if(!$result) {
			$output  = "Database query failed: " . mysql_error();
			die($output);
		}

	}
	
	public function getTemplateText($page, $type){
		$sql = "select * from template where page = '".$page."' and type = '".$type."'";
		$result = $this->query($sql);
		return $result;
	}
	
	public function getUserByLoginID($login_id){
		$sql = "select u.*, c.cid as cid, c.num_of_lang as num_of_lang from user u left join company_user cu on u.login_id = cu.login_id left join company c on cu.cid = c.cid where u.login_id = '".$login_id."' and u.active = 'Y'";
		$result = $this->query($sql);
		return $result;
	}
	
	public function getUserByLogin($login_id, $pwd){
		$login_id = $this->escape_value($login_id);
		$pwd = $this->escape_value($pwd);
		
		$sql = "select u.*, c.cid as cid, c.num_of_lang as num_of_lang from user u left join company_user cu on u.login_id = cu.login_id left join company c on cu.cid = c.cid where u.login_id = '".$login_id."' and u.password = '".$pwd."' and u.active = 'Y'";
		$result = $this->query($sql);
		if (mysql_num_rows($result) > 0){
			$row = mysql_fetch_assoc($result);
			return $row;
		}
		else{
			return null;
		}
	}
	
	public function checkUserByEmail($email){
		$email = $this->escape_value($email);
		
		$sql = "select * from user where email = '".$email."' and active = 'Y'";
		$result = $this->query($sql);
		if (mysql_num_rows($result) == 0){
			return True;
		}
		else{
			return False;
		}
	}
	
	public function addNormalUser($email, $pwd, $firstname,$lastname,$mobile){
		$sql = "insert into user (email, password, type, firstname, lastname, mobile, create_date, active) values ('".$email."', '".$pwd."', 'N','".$firstname."','".$lastname."','".$mobile."', sysdate(), 'Y')";
		$result = $this->query($sql);
		return $result;
	}	
	
	public function LogAction($id, $action, $description){
		$sql = "insert into log (create_date, id, action, description) values (sysdate(), '".$id."', '".$action."', '".$description."')";
		$result = $this->query($sql);
		return $result;
	}
	
	public function getUserType($type){
		$sql = "select * from user_type where type < '".$this->escape_value($type)."' order by type asc";
		$result = $this->query($sql);
		if (mysql_num_rows($result) > 0){
			return $result;
		}
		else{
			return null;
		}
	}
	
	public function addNewUser($login_id, $pwd, $email, $firstname, $lastname, $type, $contact_no, $address, $birthday, $cid, $note){
	
		$login_id = $this->escape_value($login_id);
		$pwd = $this->escape_value($pwd);
		$email = $this->escape_value($email);
		$firstname = $this->escape_value($firstname);
		$lastname = $this->escape_value($lastname);
		$type = $this->escape_value($type);
		$address = $this->escape_value($address);
		$birthday = $this->escape_value($birthday);
		$note = $this->escape_value($note);
	
		$sql = "insert into user (login_id, password, email, firstname, lastname, type, contact_no, address, birthday, create_date, active, note) values ('".$login_id."','". $pwd."','". $email."','". $firstname."','". $lastname."','". $type."','". $contact_no."','". $address."','". $birthday."',sysdate(), 'Y', '".$note."')";
		$result = $this->query($sql);

		$sql = "insert into company_user (login_id, cid, create_date) values ('".$login_id."','".$cid."',sysdate())";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function editUser($login_id, $email, $firstname, $lastname, $type, $contact_no, $address, $birthday, $cid, $note){
	
		$login_id = $this->escape_value($login_id);
		$pwd = $this->escape_value($pwd);
		$email = $this->escape_value($email);
		$firstname = $this->escape_value($firstname);
		$lastname = $this->escape_value($lastname);
		$type = $this->escape_value($type);
		$address = $this->escape_value($address);
		$birthday = $this->escape_value($birthday);
		$note = $this->escape_value($note);
		
	
		$sql = "update user set email = '". $email."', firstname='". $firstname."', lastname='". $lastname."', type='". $type."',contact_no='". $contact_no."',address='". $address."',birthday='". $birthday."', note='".$note."' where login_id = '".$login_id."'";
		$result = $this->query($sql);
		
		return $result;
	}

	public function matchUser($login_id, $cid){
	
		$login_id = $this->escape_value($login_id);
		$cid = $this->escape_value($cid);
	
		$sql = "select 1 from company_user where login_id = '".$login_id."' and cid = '".$cid."'";
		$result = $this->query($sql);
		$row = mysql_fetch_row($result);
		
		if ($row[0] == 0){
			$sql = "insert into company_user (login_id, cid, create_date) values ('".$login_id."','".$cid."',sysdate())";
			$result = $this->query($sql);
			
			return true;
		}
		else{
			return false;;
		}
		
	}	
	
	
	public function searchUserByLoginIDorEmail($search_type, $keyword, $company_id, $type){
	
		$keyword = $this->escape_value($keyword);
		$search_type = $this->escape_value($search_type);
		$company_id = $this->escape_value($company_id);

		$sql = "select * from view_company_user where active = 'Y' and type < '".$type."' ";
		if ($keyword != ""){
			$sql = $sql."and ".$search_type." = '".$keyword."'";
		}

		if($company_id != ""){
			$sql = $sql." and cid = '".$company_id."'";
		}
		
		$sql = $sql." order by create_date desc";

		$result = $this->query($sql);
		if (mysql_num_rows($result) > 0){
			return $result;
		}
		else{
			return null;
		}
	}
	
	public function getCompanyIdByLoginId($login_id, $cid){
		$login_id = $this->escape_value($login_id);
		$cid = $this->escape_value($cid);

		$sql = "select * from company_user where login_id = '".$login_id."' and cid = '".$cid."'";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function deactiveUser($login_id){
		$login_id = $this->escape_value($login_id);
		$sql = "update user set active='N' where login_id = '".$login_id."'";
		$result = $this->query($sql);
		
		return $result;
	}

	public function getLatestProductID($cid){
		$sql = "select count(pid) as pid from product where cid = '".$cid."'";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function addNewProduct($cid, $p_code, $p_cost, $p_price, $p_category, $p_storein, $p_expire, $p_quantity, $p_status, $p_name, $p_desc, $p_image){
		$pid = $this->escape_value($pid);
		$cid = $this->escape_value($cid);
		$p_code = $this->escape_value($p_code);
		$p_cost = $this->escape_value($p_cost);
		$p_price = $this->escape_value($p_price);
		$p_category = $this->escape_value($p_category);
		$p_storein = $p_storein;
		$p_expire = $p_expire;
		$p_quantity = $this->escape_value($p_quantity);
		$p_status = $this->escape_value($p_status);
		
		$sql = "insert into product (cid, product_code, cost, price, category_id, store_in_date, expire_date, quantity, status_id, create_date) values ";
		$sql = $sql."('". $cid."', '". $p_code."', '". $p_cost."', '". $p_price."', '". $p_category."', STR_TO_DATE('".$p_storein."', '%m/%d/%Y'), STR_TO_DATE('".$p_expire."', '%m/%d/%Y'), '". $p_quantity."', '". $p_status."', sysdate())";
		$result = $this->query($sql);
		
		$pid = $id = mysql_insert_id();
		
		for($i=0;$i<count($p_name);$i++){
			
			if(strlen(trim($p_name[$i])) > 0){
				$name = $this->escape_value($p_name[$i]);
				$desc = $this->escape_value($p_desc[$i]);
		
				$sql = "insert into product_template (pid, name, description, template, create_date) values ('".$pid."','".$name."','".$desc."','".($i+1)."',sysdate())";
				$result = $this->query($sql);
			}
			
		}
		
		for($i=0;$i<count($p_image);$i++){
			
			if(strlen(trim($p_image[$i])) > 0){
				$image = $this->escape_value($p_image[$i]);
		
				$sql = "insert into product_image (pid, image_url, create_date) values ('".$pid."','".$image."',sysdate())";
				$result = $this->query($sql);
			}
		}

		return $pid;
	}
	
	public function addNewProductTemplate(){
	}
	
	public function addNewProductImage(){
}
	
	public function updateProduct($pid, $cid, $p_code, $p_cost, $p_price, $p_category, $p_storein, $p_expire, $p_quantity, $p_status, $p_name, $p_desc, $p_image){
		$pid = $this->escape_value($pid);
		$cid = $this->escape_value($cid);
		$p_code = $this->escape_value($p_code);
		$p_cost = $this->escape_value($p_cost);
		$p_price = $this->escape_value($p_price);
		$p_category = $this->escape_value($p_category);
		$p_storein = $p_storein;
		$p_expire = $p_expire;
		$p_quantity = $this->escape_value($p_quantity);
		$p_status = $this->escape_value($p_status);
				
		$sql = "Update product set product_code = '".$p_code."', cost = '".$p_cost."', price = '".$p_price."', category_id = '".$p_category."', store_in_date = STR_TO_DATE('".$p_storein."', '%m/%d/%Y'), expire_date = STR_TO_DATE('".$p_expire."', '%m/%d/%Y'), quantity = '".$p_quantity."', status_id = '".$p_status."', modify_date = sysdate() where pid = '".$pid."'";
		$result = $this->query($sql);
		
		$sql = "delete from product_template where pid = '".$pid."'";
		$result = $this->query($sql);
		
		for($i=0;$i<count($p_name);$i++){
			
			if(strlen(trim($p_name[$i])) > 0){
				$name = $this->escape_value($p_name[$i]);
				$desc = $this->escape_value($p_desc[$i]);
		
				$sql = "insert into product_template (pid, name, description, template, create_date) values ('".$pid."','".$name."','".$desc."','".($i+1)."',sysdate())";
				$result = $this->query($sql);
			}
			
		}

		$sql = "delete from product_image where pid = '".$pid."'";
		$result = $this->query($sql);		
		for($i=0;$i<count($p_image);$i++){
			
			if(strlen(trim($p_image[$i])) > 0){
				$image = $this->escape_value($p_image[$i]);
		
				$sql = "insert into product_image (pid, image_url, create_date) values ('".$pid."','".$image."',sysdate())";
				$result = $this->query($sql);
			}
		}

		return $result;
	}
	
	public function deleteProduct($pid){
	
		$pid = $this->escape_value($pid);
		
		$sql = "Update product set deleted = 'Y' where pid = '".$pid."'";
		$result = $this->query($sql);
		
		if(mysql_affected_rows() > 0){
			return true;
		}
		else{
			return false;
		}
	}
	
	

	public function getProductCategory($cid, $template){
		$sql = "select a.category_id, name, description from product_category a, product_category_template b where a.cid = b.cid and a.cid = '".$cid."' and template = '".$template."' and status = 'Y'";
		//echo $sql;
		$result = $this->query($sql);
		
		return $result;
		
	}
	
	public function getStatus(){
		$sql = "select * from status";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getProductByPID($pid){
		
		$sql = "select * from product p left join product_template pt on p.pid = pt.pid where pt.template = 1 and p.pid='".$pid."'";
		$result = $this->query($sql);
		
		return $result;		
	}
	
	public function getProductTemplateByPID($pid){
		$sql = "select * from product_template where pid='".$pid."' order by template asc";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getProductImageByPID($pid){
		$sql = "select * from product_image where pid='".$pid."' order by create_date asc";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getProductByCodeOrName($search_type, $keyword, $cid){
	
		if($search_type == "code"){
			$cretia1 = " and p.product_code = '".$keyword."' ";
			$cretia2 = " and a.product_code = '".$keyword."' ";
		}
		
		if($search_type == "name"){
			$cretia1 = $cretia1." and pt.name like '%".$keyword."%' ";
			$cretia2 = $cretia2." and a.name like '%".$keyword."%' ";
		}
	
		$sql = "select a.pid, a.product_code, (select name from product_category_template pct where category_id = a.category_id and pct.cid = a.cid and pct.template = c.default_template) as category , a.store_in_date, a.expire_date, a.quantity, a.cost, a.price, (select status from status where status_id = a.status_id) as status, c.name, c.description from product a, (select p.pid, min(pt.template) as default_template, pt.name, pt.description from product p, product_template pt where p.pid = pt.pid ".$cretia1." group by p.pid) c where a.pid = c.pid and a.cid = '".$cid."' and deleted = 'N' ".$cretia2." order by create_date desc, modify_date desc";
		
		//echo $sql;
		
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getProductCategoryByCID($cid){
		$sql = "select a.*, b.name, b.description from product_category a, product_category_template b where a.category_id = b.category_id and b.template = 1 and a.cid = '".$cid."' and status = 'Y'";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function deactiveProductCategory($category_id){
		$sql = "update product_category set status = 'N', modify_date = sysdate() where category_id = '".$category_id."'";
		$result = $this->query($sql);
		
		if(mysql_affected_rows() > 0){
			return true;
		}
		else{
			return false;
		}
	}
	
	public function getProductCategoryTemplateById($category_id){
		$sql = "select * from product_category_template where category_id = '".$category_id."' order by template asc";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function addNewProductCategory($cat_name, $cat_desc, $cid){
		
		$sql = "insert into product_category (cid, create_date) value ('".$cid."', sysdate())";
		$result = $this->query($sql);
		
		$id = mysql_insert_id();
		
		for($i=0;$i<count($cat_name);$i++){
			
			if(strlen(trim($cat_name[$i])) > 0){
				$name = $this->escape_value($cat_name[$i]);
				$desc = $this->escape_value($cat_desc[$i]);
		
				$sql = "insert into product_category_template (cid, name, description, template, create_date, category_id) values ('".$cid."','".$name."','".$desc."','".($i+1)."',sysdate(), '".$id."')";
				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function updateProductCategory($cat_name, $cat_desc, $cid, $category_id){
		
		$sql = "delete from product_category_template where cid = '".$cid."' and category_id = '".$category_id."'";
		$result = $this->query($sql);
			
		for($i=0;$i<count($cat_name);$i++){
			
			if(strlen(trim($cat_name[$i])) > 0){
				$name = $this->escape_value($cat_name[$i]);
				$desc = $this->escape_value($cat_desc[$i]);
		
				$sql = "insert into product_category_template (cid, name, description, template, create_date, category_id) values ('".$cid."','".$name."','".$desc."','".($i+1)."',sysdate(), '".$category_id."')";
				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	
	
	public function getLocationByName($name){

		$sql = "select l.*, a.name as area_name from location l, area a where l.aid = a.aid";
		
		if($name != ""){
			$name = $this->escape_value($name);
			$sql = $sql." where l.name like '%".$name."%'";
		}
		
		$result = $this->query($sql);
		return $result;
	
	}
	
	public function getAreaById($aid){
		$sql = "select * from area";
		
		if($aid != ""){
			$sql = $sql." where aid = '".$aid."'";
		}
		
		$result = $this->query($sql);
		return $result;
	}
	
	public function getAreaByName($name){

		$sql = "select * from area where 1=1 ";
		
		if($name != ""){
			$name = $this->escape_value($name);
			$sql = $sql." and name like '%".$name."%'";
		}
		
		$result = $this->query($sql);
		return $result;
	
	}	
	
	public function addNewArea($area_name){
		
		$sql = "select IFNULL(max(aid), 0) as max_aid from area";
		$result = $this->query($sql);
		$row = mysql_fetch_assoc($result);
		
		$aid = $row["max_aid"] + 1;
		
		for($i=0;$i<count($area_name);$i++){
			if(strlen(trim($area_name[$i])) > 0){
				$name = $this->escape_value($area_name[$i]);
				
				$sql = "insert into area (aid, name, template, create_date) values ('".$aid."','".$name."','".($i+1)."',sysdate())";

				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function updateArea($aid, $area_name){
		
		$sql = "delete from area where aid = '".$aid."'";
		$result = $this->query($sql);
			
		for($i=0;$i<count($area_name);$i++){
			
			if(strlen(trim($area_name[$i])) > 0){
				$name = $this->escape_value($area_name[$i]);
		
				$sql = "insert into area (aid, name, template, create_date) values ('".$aid."','".$name."','".($i+1)."',sysdate())";
				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function getArea(){
		$sql = "select * from area";
		
		$result = $this->query($sql);
		return $result;
	}
	
	public function addNewLocation($location_name, $area){
		
		$sql = "select IFNULL(max(lid), 0) as max_lid from location";
		$result = $this->query($sql);
		$row = mysql_fetch_assoc($result);
		
		$lid = $row["max_lid"] + 1;
		
		for($i=0;$i<count($location_name);$i++){
			if(strlen(trim($location_name[$i])) > 0){
				$name = $this->escape_value($location_name[$i]);
				
				$sql = "insert into location (lid, name, template, aid, create_date) values ('".$lid."','".$name."','".($i+1)."', '".$area."', sysdate())";

				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function updateLocation($lid, $location_name, $area){
		
		$sql = "delete from location where lid = '".$lid."'";
		$result = $this->query($sql);
			
		for($i=0;$i<count($location_name);$i++){
			
			if(strlen(trim($location_name[$i])) > 0){
				$name = $this->escape_value($location_name[$i]);
		
				$sql = "insert into location (lid, name, template, aid, create_date) values ('".$lid."','".$name."','".($i+1)."','".$area."' ,sysdate())";
				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function getLocationById($lid){
		$sql = "select * from location where 1=1";
		
		if($lid != ""){
			$sql = $sql." and lid = '".$lid."'";
		}
		
		$result = $this->query($sql);
		return $result;
	}
	
	public function getCompanyCategoryById($category_id, $cid){
		$sql = "select * from company_category where cid = '".$cid."'";
		
		if($category_id != ""){
			$sql = $sql." category_id = '".$category_id."'";
		}
		
		$result = $this->query($sql);
		return $result;
	}
	
	public function addNewCompanyCategory($category_name){
		
		$sql = "select IFNULL(max(category_id), 0) as max_category_id from company_category";
		$result = $this->query($sql);
		$row = mysql_fetch_assoc($result);
		
		$category_id = $row["max_category_id"] + 1;
		
		for($i=0;$i<count($category_name);$i++){
			if(strlen(trim($category_name[$i])) > 0){
				$name = $this->escape_value($category_name[$i]);
				
				$sql = "insert into company_category (category_id, name, template, create_date) values ('".$category_id."','".$name."','".($i+1)."',sysdate())";

				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function updateCompanyCategory($cid, $category_name){
		$sql = "delete from company_category where category_id = '".$cid."'";
		$result = $this->query($sql);
			
		for($i=0;$i<count($category_name);$i++){
			
			if(strlen(trim($category_name[$i])) > 0){
				$name = $this->escape_value($category_name[$i]);
		
				$sql = "insert into company_category (category_id, name, template, create_date) values ('".$cid."','".$name."','".($i+1)."',sysdate())";
				$result = $this->query($sql);
			}
		}
		
		return true;
	}
	
	public function getCompanyByName($cid, $company_name){
	
		$sql = "select * form company where name = '".company_name."'";
		
		if($cid != ""){
				$sql = $sql." and cid <> '".$cid."'";
		}

		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getCompanyByCID($cid){
		$sql = "select * from company where cid = '".$cid."'";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getCompanyTemplateByCID($cid){
		$sql = "select * from company_template where cid = '".$cid."'";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function addNewCompany($company_code,$company_name,$company_desc,$email,$phone_no,$address,$location,$category,$num_language){
	
		$sql = "select IFNULL(max(cid), 0) as max_cid from company";
		$result = $this->query($sql);
		$row = mysql_fetch_assoc($result);
		
		$cid = $row["max_cid"] + 1;
	
		$sql = "insert into company (cid, code, phone_no, email, location, address, category_id, num_of_lang, active, create_date) values ('".$cid."', '".$company_code."', '".$phone_no."', '".$email."', '".$location."', '".$address."', '".$category."', '".$num_language."', 'Y', sysdate())";
		$result = $this->query($sql);
		
		$sql = "insert into company_template (cid, name, description, address, template, create_date) values ('".$cid."', '".$company_name."', '".$company_desc."', '".$address."', '1', sysdate())";
		$result = $this->query($sql);
		
		return $cid;
	}
	
	public function getCompanyByNameOrCode($cid, $type, $keyword){
		
		$cretia = "";
		if($type != ""){
			if($type == "name"){
				$cretia = "name = '".$keyword."'";
			}
			if($type == "code"){
				$cretia = "code = '".$keyword."'";
			}
		}
	
		$sql = "select * from company c, company_template ct where c.cid=ct.cid and c.cid = '".$cid."' and ct.template = 1 ".$cretia." order by c.create_date desc, name asc ";
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function updateCompany($cid, $company_code,$company_name,$company_desc,$email,$phone_no,$address,$location,$category,$num_language){
		
		$sql = "update company set code = '".$company_code."', phone_no = '".$phone_no."', email = '".$email."', location = '".$location."', address = '".$address."', category_id = '".$category."', num_of_lang = '".$num_language."' where cid = '".$cid."'";
		$result = $this->query($sql);
		
		$sql = "delete from company_template where cid = '".$cid."'";
		$result = $this->query($sql);
		
		for($i=0;$i<count($company_name);$i++){
			
			if(strlen(trim($company_name[$i])) > 0){
				$name = $this->escape_value($company_name[$i]);
				$description = $this->escape_value($company_desc[$i]);
				$comp_address = $this->escape_value($address[$i]);
		
				$sql = "insert into company_template (cid, name, description, address, template, create_date) values ('".$cid."', '".$name."', '".$description."', '".$comp_address."', '".($i+1)."', sysdate())";
				$result = $this->query($sql);
			}
		}

		return $cid;
	}
	
	public function updateCompanyLogo($cid, $logo_url){
		$sql = "update company set logo = '".$logo_url."' where cid = '".$cid."'";
		$result = $this->query($sql);
		
		return true;
	}
	
	public function getDiscountByCode($cid, $promo_code){
		
		$cretia = "";
		if($promo_code != ""){
			$promo_code = $this->escape_value($promo_code);
			$cretia = " and promo_code = '".$promo_code."'";
		}
	
		$sql = "select p.pid, cid, type, promo_code, start_date, expire_date, limit_number, description from promotion p left join promotion_template pt on p.pid = pt.pid where template = 1 and active = 'Y' and cid = '".$cid."' ".$cretia;

		$result = $this->query($sql);
		
		return $result;
	}
	
	public function getDiscountForCalculate($id, $cid){
		$sql = "select pid, type, CONCAT_WS(',', field_a, field_b, field_c) as discount from promotion where pid = '".$pid."' and cid = '".$cid."' and active = 'Y'";
		//echo $sql;
		$result = $this->query($sql);
		
		return $result;
	}
	
	public function addDiscount($cid, $promo_code, $promo_desc, $promo_type, $start_date, $end_date, $limit, $field_a, $field_b, $field_c, $target, $create_by){
		
		$promo_code = $this->escape_value($promo_code);
		
		$limit = $this->escape_value($limit);
		$field_a = $this->escape_value($field_a);
		$field_b = $this->escape_value($field_b);
		$field_c = $this->escape_value($field_c);
		$target = $this->escape_value($target);
		$create_by = $this->escape_value($create_by);
		
		$sql = "insert into promotion (cid, type, promo_code, start_date, expire_date, limit_number, field_a, field_b, field_c, target_user, create_by, create_date, active) values ( '".$cid."', '".$promo_type."',  '".$promo_code."', STR_TO_DATE('".$start_date."', '%d/%m/%Y'), STR_TO_DATE('".$end_date."', '%d/%m/%Y'), '".$limit."', '".$field_a."', '".$field_b."', '".$field_c."', '".$target."', '".$create_by."', sysdate(), 'Y')";

		$result = $this->query($sql);
		
		$pid = mysql_insert_id();
		
		for($i=0;$i<count($promo_desc);$i++){
			$tempValue = $this->escape_value($promo_desc[$i]);
			$sql = "insert into promotion_template (pid, description, template) values ('".$pid."', '".$promo_desc[$i]."', '".($i+1)."')";
			$result = $this->query($sql);
		}
		
		return true;
	}
	
	public function deleteDiscount($pid, $cid){
		$pid = $this->escape_value($pid);
		
		$sql = "update promotion set active = 'N' where cid='".$cid."' and pid = '".$pid."'";
		
		$result = $this->query($sql);
		
		return true;
	}
	
	public function addInvoice($cid, $discount, $total, $uid, $product_list){
		$sql = "insert into invoice (cid, discount, total, invoice_date, status_id, active, uid, create_date) values ('".$cid."', '".$discount."', '".$total."', sysdate(), '1', 'Y', '".$uid."', sysdate())";
		$result = $this->query($sql);
		$id = mysql_insert_id();
		
		foreach ($product_list as &$product) {
			$sql = "insert into invoice_product (id, pid, quantity, create_date) values ('".$id."', '".$product[0]."', '".$product[1]."', sysdate())";
			$result = $this->query($sql);
		}
		
		return true;
	}
	
	public function getDataByDate($date_from, $date_to, $cid){
		$sql = "select pid, sum(quantity) as p_sum from invoice_product ip inner join invoice i on ip.id = i.id and i.cid = '".$cid."' group by pid";
		$result = $this->query($sql);
		
		return $result;
	}
	
}
?>